﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using Pixysoft.Framework.XSchEmA.Entity;
using System.Data;

namespace Pixysoft.Framework.XSchEmA.Database
{
    internal class OleDbSchemaDownloader
    {
        public void GetTables(OleDbConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OleDbSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, user, null, "TABLE" });
                foreach (DataRow row in table.Rows)
                {
                    string tablename = row["TABLE_NAME"].ToString().ToUpper().Trim();

                    //if (tablename.Contains("BIN$")) //自己在adapter里面修改！
                    //    continue;

                    //2010-08-22 过滤access的关键表

                    if (tablename.Equals("MSysAccessXML", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (tablename.Equals("MSysAccessObjects", StringComparison.OrdinalIgnoreCase))
                        continue;

                    TableSchema tableSchema = new TableSchema(tablename);
                    tableSchema.Comment = row["DESCRIPTION"].ToString();
                    schema.Tables.Add(tableSchema);
                }
            }
        }

        public void GetColumns(IOleDbAnalyzer analyzer, OleDbConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OleDbSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, user });

                foreach (DataRow row in table.Rows)
                {
                    string tableName = row["TABLE_NAME"].ToString();

                    if (tableName.Equals("MSysAccessXML", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (tableName.Equals("MSysAccessObjects", StringComparison.OrdinalIgnoreCase))
                        continue;


                    TableSchema tableSchema = schema.GetTable(tableName);

                    if (tableSchema == null)
                    {
                        LoggerHelper.Info(tableName + " doesn't exist!");

                        continue;
                    }

                    ColumnSchema columnSchema = new ColumnSchema();
                    columnSchema.TableName = tableSchema.TableName;
                    columnSchema.ColumnName = row["COLUMN_NAME"] == DBNull.Value || row["COLUMN_NAME"] == null ? null : row["COLUMN_NAME"].ToString().ToUpper().Trim();
                    columnSchema.IsNullable = row["IS_NULLABLE"] == DBNull.Value || row["IS_NULLABLE"] == null ? true : bool.Parse(row["IS_NULLABLE"].ToString().ToUpper().Trim());

                    analyzer.SetDataType(columnSchema,
                        row["COLUMN_FLAGS"] == DBNull.Value || row["COLUMN_FLAGS"] == null ? -1 : int.Parse(row["COLUMN_FLAGS"].ToString().ToUpper().Trim()),
                        row["DATA_TYPE"] == DBNull.Value || row["DATA_TYPE"] == null ? -1 : int.Parse(row["DATA_TYPE"].ToString().ToUpper().Trim()),
                        row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value ||
                        row["CHARACTER_MAXIMUM_LENGTH"] == null ||
                        row["CHARACTER_MAXIMUM_LENGTH"].ToString() == "" ? -1 : Int64.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString().ToUpper().Trim()),
                        row["NUMERIC_PRECISION"] == DBNull.Value ||
                        row["NUMERIC_PRECISION"] == null ||
                        row["NUMERIC_PRECISION"].ToString() == "" ? -1 : int.Parse(row["NUMERIC_PRECISION"].ToString().ToUpper().Trim()),
                        row["NUMERIC_SCALE"] == DBNull.Value ||
                        row["NUMERIC_SCALE"] == null ||
                        row["NUMERIC_SCALE"].ToString() == "" ? -1 : int.Parse(row["NUMERIC_SCALE"].ToString().ToUpper().Trim()),
                        row["DATETIME_PRECISION"] == DBNull.Value ||
                        row["DATETIME_PRECISION"] == null ||
                        row["DATETIME_PRECISION"].ToString() == "" ? -1 : int.Parse(row["DATETIME_PRECISION"].ToString().ToUpper().Trim()));

                    columnSchema.Comment = row["DESCRIPTION"].ToString();
                    columnSchema.DefaultValue = row["COLUMN_DEFAULT"].ToString() == "" ? null : row["COLUMN_DEFAULT"].ToString().ToUpper().Trim();


                    SchemaUtility.AddToList<ColumnSchema>(tableSchema.Columns, columnSchema, int.Parse(row["ORDINAL_POSITION"].ToString()) - 1);
                }

            }
        }

        public void GetPrimaryKeys(OleDbConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OleDbSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, user });

                foreach (DataRow row in table.Rows)
                {
                    string tableName = row["TABLE_NAME"].ToString().ToUpper().Trim();
                    string columnName = row["COLUMN_NAME"].ToString().ToUpper().Trim();
                    string pkName = row["PK_NAME"].ToString().ToUpper().Trim();
                    int index = int.Parse(row["ORDINAL"].ToString().ToUpper().Trim()) - 1;

                    if (!schema.ContainedTable(tableName))
                    {
                        LoggerHelper.Info(tableName);
                        continue;
                    }

                    PrimaryKeySchema pkSchema = schema.GetPrimaryKeyByName(pkName);

                    if (pkSchema == null)
                    {
                        pkSchema = new PrimaryKeySchema();
                        pkSchema.ConsName = pkName;
                        pkSchema.TableName = tableName;
                        schema.PrimaryKeys.Add(pkSchema);
                    }

                    SchemaUtility.AddToList<string>(pkSchema.ColumnNames, columnName, index);
                }
            }
        }

        /// <summary>
        /// 无法区分Unique Constraint,Unique Index，因此全部看成constraint
        /// </summary>
        /// <param name="analyzer"></param>
        /// <param name="conn"></param>
        /// <param name="schema"></param>
        /// <param name="user"></param>
        public void GetUniques(IOleDbAnalyzer analyzer, OleDbConnection conn, DatabaseSchema schema, string user)
        {
            //必须发生在pk之后

            lock (typeof(OleDbSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, new object[] { null, user });

                foreach (DataRow row in table.Rows)
                {
                    if (!analyzer.IsTrue(row["UNIQUE"].ToString()))
                        continue;

                    if (analyzer.IsTrue(row["PRIMARY_KEY"].ToString()))
                        continue;

                    string indexName = row["INDEX_NAME"].ToString();
                    string tableName = row["TABLE_NAME"].ToString();
                    string columnName = row["COLUMN_NAME"].ToString();
                    int index = int.Parse(row["ORDINAL_POSITION"].ToString().ToUpper().Trim()) - 1;

                    if (!schema.ContainedTable(tableName))
                    {
                        LoggerHelper.Info(tableName);
                        continue;
                    }

                    if (schema.ContainedPrimaryKey(indexName))
                    {
                        LoggerHelper.Info(indexName);
                        continue;
                    }

                    UniqueSchema unique = schema.GetUniqueByName(indexName);

                    if (unique == null)
                    {
                        unique = new UniqueSchema();
                        unique.ConsName = indexName;
                        unique.TableName = tableName;
                        schema.Uniques.Add(unique);
                    }

                    SchemaUtility.AddToList<string>(unique.ColumnNames, columnName, index);
                }
            }
        }

        public void GetForeignKeys(OleDbConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OleDbSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, user });

                foreach (DataRow row in table.Rows)
                {
                    string fkTableName = row["FK_TABLE_NAME"].ToString();
                    string fkColumnName = row["FK_COLUMN_NAME"].ToString();
                    string fkName = row["FK_NAME"].ToString();
                    string constraintName = row["PK_NAME"].ToString();
                    int index = int.Parse(row["ORDINAL"].ToString().ToUpper().Trim()) - 1;

                    if (!schema.ContainedTable(fkTableName))
                    {
                        LoggerHelper.Info("MISSING: fkTableName");
                        continue;
                    }

                    ForeignKeySchema fkSchema = schema.GetForeignKeyByName(fkName);

                    if (fkSchema == null)
                    {
                        fkSchema = new ForeignKeySchema();
                        fkSchema.ConsName = fkName;
                        fkSchema.FkTableName = fkTableName;
                        fkSchema.PkeyConsName = constraintName;
                        schema.ForeignKeys.Add(fkSchema);
                    }

                    SchemaUtility.AddToList<string>(fkSchema.FkColumnNames, fkColumnName, index);
                }
            }
        }
    }

    interface IOleDbAnalyzer
    {
        void SetDataType(ColumnSchema column, int flag, int DATA_TYPE, long length, int precision, int scale, int datePrecision);

        bool IsTrue(string value);
    }

    internal class OleDbOracleAnalyzer : IOleDbAnalyzer
    {
        void IOleDbAnalyzer.SetDataType(ColumnSchema column, int flag, int DATA_TYPE, long length, int precision, int scale, int datePrecision)
        {
            switch (DATA_TYPE)
            {

                case 5:
                    {
                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.FLOAT.ToString(), -1, precision, -1);
                        break;
                    }
                case 13:
                    {
                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.UROWID.ToString(), -1, -1, -1);
                        break;
                    }
                case 128:
                    {
                        //C_RAW			7	FALSE		104	TRUE	128		13	13
                        //C_BLOB			13	FALSE		232	TRUE	128		4294967294	4294967294
                        //C_BFILE			14	FALSE		232	TRUE	128		4294967294	4294967294
                        switch (flag)
                        {
                            case 7:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.RAW.ToString(), length, -1, -1);
                                    break;
                                }
                            default:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.BLOB.ToString(), length, -1, -1);
                                    break;
                                }
                        }
                        break;

                    }
                case 129:
                    {
                        switch (flag)
                        {
                            //C_CHAR_CHAR			20	FALSE		120	TRUE	129		26	26
                            //C_CHAR			1	FALSE		120	TRUE	129		13	13
                            case 24:
                            case 120:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.CHAR.ToString(), length, -1, -1);
                                    break;
                                }
                            //C_VARCHAR2_CHAR			21	FALSE		104	TRUE	129		26	26
                            //C_VARCHAR2			2	FALSE		104	TRUE	129		13	13
                            case 104:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.VARCHAR2.ToString(), length, -1, -1);
                                    break;
                                }
                            //C_LONG			6	FALSE		232	TRUE	129		2147483647	2147483647
                            //C_CLOB			11	FALSE		232	TRUE	129		4294967294	4294967294
                            case 232:
                                {
                                    if (length > 2147483647)
                                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.CLOB.ToString(), length, -1, -1);
                                    else
                                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.LONG.ToString(), length, -1, -1);
                                    break;
                                }
                            //C_ROWID			9	FALSE		376	TRUE	129		18	18
                            case 376:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.ROWID.ToString(), length, -1, -1);
                                    break;
                                }
                            default:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.VARCHAR2.ToString(), length, -1, -1);
                                    break;
                                }
                        }

                        break;
                    }
                case 130:
                    {
                        //C_NVARCHAR2			8	FALSE		104	TRUE	130		26	52
                        //C_NCHAR			10	FALSE		120	TRUE	130		26	52
                        //C_NCLOB			12	FALSE		232	TRUE	130		4294967294	4294967294
                        switch (flag)
                        {
                            case 104:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.NVARCHAR2.ToString(), length, -1, -1);
                                    break;
                                }
                            case 120:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.NCHAR.ToString(), length, -1, -1);
                                    break;
                                }
                            case 232:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.NCLOB.ToString(), length, -1, -1);
                                    break;
                                }
                            default:
                                {
                                    column.DataType = new DataTypeSchema(OracleDataTypeCollection.NVARCHAR2.ToString(), length, -1, -1);
                                    break;
                                }
                        }

                        break;
                    }
                case 131:
                    {
                        //C_NUMBER			3	FALSE		120	TRUE	131				13	3
                        //C_INTEGER			4	FALSE		120	TRUE	131				38	0
                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.NUMBER.ToString(), -1, precision, scale);
                        break;
                    }
                case 135:
                    {
                        //C_DATE			5	FALSE		120	TRUE	135						0
                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.DATE.ToString(), -1, -1, -1);
                        break;
                    }
                default:
                    {
                        column.DataType = new DataTypeSchema(OracleDataTypeCollection.VARCHAR2.ToString(), 50, -1, -1);
                        break;
                    }
            }
        }

        bool IOleDbAnalyzer.IsTrue(string value)
        {
            try
            {
                return int.Parse(value) < 0; //for OraOleDb.Oracle only!
            }
            catch
            {
                return bool.Parse(value);
            }
        }

    }

    internal class OleDbAccessAnalyzer : IOleDbAnalyzer
    {
        public OleDbAccessAnalyzer()
        {
        }


        void IOleDbAnalyzer.SetDataType(ColumnSchema column, int flag, int DATA_TYPE, long length, int precision, int scale, int datePrecision)
        {
            switch (DATA_TYPE)
            {
                case 11://C_BOOLEAN
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.YESNO.ToString(), -1, -1, -1);
                    break;
                case 3://C_COUNTER LONG
                    if (flag != 90)
                    {
                        column.DataType = new DataTypeSchema(AccessDataTypeCollection.INT.ToString(), -1, -1, -1);
                        break;
                    }

                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.COUNTER.ToString(), -1, -1, -1, true);
                    break;
                case 6://C_CURRENCY
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.CURRENCY.ToString(), -1, -1, -1);
                    break;
                case 7://C_DATETIME
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.DATETIME.ToString(), -1, -1, -1);
                    break;
                case 131://C_DECIMAL
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.NUMERIC.ToString(), -1, precision, scale);
                    break;
                case 5://C_DOUBLE
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.DOUBLE.ToString(), -1, -1, -1);
                    break;
                case 2://C_INT
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.SMALLINT.ToString(), -1, -1, -1);
                    break;
                case 130://C_MEMO TEXT
                    //[20080628] 使用length判断

                    //09-11-01 LENGTH = 0 == MEMO
                    if (length > 0)
                    {
                        column.DataType = new DataTypeSchema(AccessDataTypeCollection.TEXT.ToString(), length, -1, -1); /// [20080510]不除以(Int64)2

                    }
                    else
                    {
                        column.DataType = new DataTypeSchema(AccessDataTypeCollection.MEMO.ToString(), -1, -1, -1);
                    }
                    //switch (flag)
                    //{
                    //    case 106://C_TEXT
                    //        column.DataType = new DataTypeSchema(AccessDataTypeCollection.TEXT.ToString(), length, -1, -1); /// [20080510]不除以(Int64)2
                    //        break;
                    //    default:
                    //    case 234://C_MEMO
                    //        column.DataType = new DataTypeSchema(AccessDataTypeCollection.MEMO.ToString(), -1, -1, -1);
                    //        break;
                    //}
                    break;
                case 128://C_OLE
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.IMAGE.ToString(), -1, -1, -1);
                    break;
                case 4://C_SINGLE
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.SINGLE.ToString(), -1, -1, -1);
                    break;
                case 17://C_TINYINT
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.BYTE.ToString(), -1, -1, -1);
                    break;

                default:
                    column.DataType = new DataTypeSchema(AccessDataTypeCollection.MEMO.ToString(), -1, -1, -1);
                    break;

            }


        }

        bool IOleDbAnalyzer.IsTrue(string value)
        {
            return bool.Parse(value);
        }
    }
}
